from openpyxl.utils import range_boundaries
from openpyxl.worksheet.worksheet import Worksheet

from .schema import (
    SummaryProduct,
    BidProduct,
    Offer,
    ProductDetail,
    OfferDetail,
    SummaryTable,
    BidTable,
    START_ROW_OF_DATA,
    ExceptionOfferDetail,
    ExceptionOffer,
    NO_UNIT_PRICE_STATUS,
)


def import_products_from_tender(sheet: Worksheet) -> SummaryTable:
    table = SummaryTable(table_name=sheet.title)

    row_of_brand = 0
    row_of_weight = 0
    for cell in sheet[2]:
        if cell.value and "品牌" in str(cell.value):
            row_of_brand = cell.column - 1
        if cell.value and "理论重量" in str(cell.value):
            row_of_weight = cell.column - 1

    for row in sheet.iter_rows(min_row=START_ROW_OF_DATA, max_row=sheet.max_row):
        is_available = row[0].value and row[1].value and row[2].value
        if not is_available:
            continue

        detail = ProductDetail(
            order_num=row[0].value,
            name=row[1].value,
            spec=row[2].value,
            unit=row[3].value,
            theoretical_weight=row[row_of_weight].value if row_of_weight > 0 else None,
            required_brand=row[row_of_brand].value if row_of_brand > 0 else None,
        )
        try:
            product = SummaryProduct(
                key_name=detail.name, key_spec=detail.spec, detail=detail, offers=[]
            )
            table.products.append(product)
        except Exception as e:
            print(
                f"Error creating SummaryProduct for row {row[0].row}: {e}. sheet title: {sheet.title}"
            )

    return table


def import_products_from_bid(
    sheet: Worksheet, supplier: str, is_bargaining: bool = False
) -> BidTable:
    table = BidTable(table_name=sheet.title, supplier=supplier)

    col_of_brand = 0
    col_of_weight = 0
    col_of_price = 0
    col_of_remark = 0
    for cell in sheet[2]:
        if cell.value and "品牌" in str(cell.value):
            col_of_brand = cell.column - 1
        elif cell.value and "理论重量" in str(cell.value):
            col_of_weight = cell.column - 1
        elif cell.value and "单价" in str(cell.value):
            col_of_price = cell.column - 1
        elif cell.value and "备注" in str(cell.value):
            col_of_remark = cell.column - 1

    for row in sheet.iter_rows(min_row=START_ROW_OF_DATA, max_row=sheet.max_row):
        is_available = row[0].value and row[1].value and row[2].value
        if not is_available:
            continue

        detail = ProductDetail(
            order_num=row[0].value,
            name=row[1].value,
            spec=row[2].value,
            unit=row[3].value,
            theoretical_weight=row[col_of_weight].value if col_of_weight > 0 else None,
            required_brand=None,
        )

        value = row[col_of_price].value
        if value in NO_UNIT_PRICE_STATUS:
            exception_detail = ExceptionOfferDetail(
                unit_price=value,
                brand=row[col_of_brand].value if col_of_brand > 0 else "",
                remark=row[col_of_remark].value if col_of_remark > 0 else "",
            )
            exception_offer = ExceptionOffer(
                status=0,
                supplier=supplier,
                key_price=value,
                is_bargaining=is_bargaining,
                detail=exception_detail,
            )
            product = BidProduct(
                key_name=detail.name,
                key_spec=detail.spec,
                detail=detail,
                offer=exception_offer,
            )
            table.products.append(product)
        elif not isinstance(value, (int, float)):
            # 非法报价
            exception_detail = ExceptionOfferDetail(
                unit_price=row[col_of_price].value,
                brand=row[col_of_brand].value if col_of_brand > 0 else "",
                remark=row[col_of_remark].value if col_of_remark > 0 else "",
            )
            exception_offer = ExceptionOffer(
                supplier=supplier,
                key_price=exception_detail.unit_price,
                is_bargaining=is_bargaining,
                detail=exception_detail,
                status=-1,
            )
            product = BidProduct(
                key_name=detail.name,
                key_spec=detail.spec,
                detail=detail,
                offer=exception_offer,
            )
            table.products.append(product)
        else:
            offer_detail = OfferDetail(
                unit_price=row[col_of_price].value,
                brand=row[col_of_brand].value if col_of_brand > 0 else "",
                remark=row[col_of_remark].value if col_of_remark > 0 else "",
            )
            offer = Offer(
                supplier=supplier,
                key_price=offer_detail.unit_price,
                is_bargaining=is_bargaining,
                detail=offer_detail,
            )
            product = BidProduct(
                key_name=detail.name, key_spec=detail.spec, detail=detail, offer=offer
            )
            table.products.append(product)

    return table


def unmerge_and_fill_cells(worksheet):
    """
    拆分指定工作表中的所有合并单元格，并将拆分后的单元格赋值为原合并单元格的值.

    Args:
        worksheet (openpyxl.worksheet.worksheet.Worksheet): 要处理的工作表对象
    """
    # 获取所有合并单元格区域
    merged_ranges = list(worksheet.merged_cells.ranges)

    # 遍历所有合并单元格
    for merged_range in merged_ranges:
        # 获取合并区域的范围
        min_col, min_row, max_col, max_row = range_boundaries(str(merged_range))

        # 获取合并单元格的值（来自左上角单元格）
        merged_value = worksheet.cell(row=min_row, column=min_col).value

        # 取消合并单元格
        worksheet.unmerge_cells(str(merged_range))

        # 将拆分后的所有单元格赋值为原合并单元格的值
        for row in range(min_row, max_row + 1):
            for col in range(min_col, max_col + 1):
                worksheet.cell(row=row, column=col).value = merged_value


if __name__ == "__main__":
    from openpyxl import load_workbook

    wb = load_workbook("C:\\Users\\one\\Desktop\\main.xlsx")
    sheet = wb.active
    unmerge_and_fill_cells(sheet)
    # table = import_products_from_tender(sheet)
    # print(table.model_dump_json(indent=2))
    table = import_products_from_bid(sheet, "供应商A")
    print(table.model_dump_json(indent=2))
